﻿/**********************************************************************************
 * 程序说明：     返回Sql语句操作类
 * 创建日期：     2009.6.13
 * 修改日期：     2010.12.16
 * 程序制作：     agui
 * 联系方式：     mailto:354990393@qq.com  
 * ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using PlugNT.Safe;
using PlugNT.Common;

namespace PlugNT.Database
{
    /// <summary>
    /// 返回Sql语句操作类(access不能使用,因为多个left join 要加括号)
    /// </summary>
    public class Sql
    {
        #region 按ID升序排序


        public static string GetPagingString(int topCount, int currPageCount, string tableName, string idName, string andWhere)
        {
            return GetPagingString(topCount, currPageCount, tableName, idName, "*", andWhere);
        }



        public static string GetPagingString(int topCount, int currPageCount, string tableName, string idName, string colNames, string andWhere)
        {
            StringBuilder sql = new StringBuilder();
            string orderStr = " order by " + idName + " asc";
            sql.Append("select top " + topCount.ToString() + "  " + colNames + "  from " + tableName);
            if (currPageCount >= 1)
            {
                sql.Append(" where " + idName + " >(");
                sql.Append("select max(" + idName + ") from (");
                sql.Append("select top " + (topCount * currPageCount).ToString() + " " + idName + " from " + tableName + " where 1=1 " + andWhere); // + orderStr
                sql.Append(") ast) " + andWhere + orderStr);
                //select top 3 * from [table] where id >(select max(id) from (select top 3 id from [table] where 1=1 )ast)
            }
            else
            {
                sql.Append("  where 1=1 " + andWhere + orderStr);
            }
            return sql.ToString();
        }




        public static string GetPagingString(int topCount, int currPageCount, string tableName, string idName, string colNames,string andWhere, LeftJoinInfo joinValue)
        {

            StringBuilder sql = new StringBuilder();
            string joinAndWhere =(" "+ andWhere).Replace(" and ", " and a.");
            string orderStr = " order by a." + idName + " asc";
            string joinInfoAndWhere = joinValue.ToJoinAndWhereString();

            sql.Append("select top " + topCount.ToString() + "  a." + colNames.Replace(",", ",a.") + "," + joinValue.ToJoinColumnsString() + "  from " + tableName + " as a " + joinValue.ToJoinTableString(""));
            if (currPageCount >= 1)
            {
                sql.Append(" where a." + idName + " >(");
                sql.Append("select max(" + idName + ") from (");
                sql.Append("select top " + (topCount * currPageCount).ToString() + " " + idName + " from " + tableName + " where 1=1 " + andWhere);
                sql.Append(") ast) " + joinAndWhere + joinInfoAndWhere + orderStr);
            }
            else
            {
                sql.Append("  where 1=1 " + joinAndWhere + joinInfoAndWhere + orderStr);
            }
            return sql.ToString();

        }

        #endregion

        #region 按ID降序排序

        public static string GetRePagingString(int topCount, int currPageCount, string tableName, string idName, string andWhere)
        {
            return GetRePagingString(topCount, currPageCount, tableName, idName, "*", andWhere);
        }


        public static string GetRePagingString(int topCount, int currPageCount, string tableName, string idName, string colNames, string andWhere)
        {
            StringBuilder sql = new StringBuilder();
            string orderStr = " order by " + idName + " desc";
            sql.Append("select top " + topCount.ToString() + "  " + colNames + "  from " + tableName);
            if (currPageCount >= 1)
            {
                sql.Append(" where " + idName + " <(");
                sql.Append("select min(" + idName + ") from (");
                sql.Append("select top " + (topCount * currPageCount).ToString() + " " + idName + " from " + tableName + " where 1=1 " + andWhere + orderStr);
                sql.Append(") ast) " + andWhere + orderStr);
                //select top 3 * from [table] where id >(select max(id) from (select top 3 id from [table] where 1=1 )ast)
            }
            else
            {
                sql.Append("  where 1=1 " + andWhere + orderStr);
            }
            return sql.ToString();
        }



        public static string GetRePagingString(int topCount, int currPageCount, string tableName, string idName, string colNames, string andWhere, LeftJoinInfo joinValue)
        {

            StringBuilder sql = new StringBuilder();
            string orderStr = " order by " + idName + " desc";
            string orderJoinStr = " order by a." + idName + " desc";

            string joinAndWhere = (" " + andWhere).Replace(" and ", " and a.");
            string joinInfoAndWhere = joinValue.ToJoinAndWhereString();


            sql.Append("select top " + topCount.ToString() + "  a." + colNames.Replace(",", ",a.") + "," + joinValue.ToJoinColumnsString() + "  from " + tableName + " as a " + joinValue.ToJoinTableString(""));
            if (currPageCount >= 1)
            {
                sql.Append(" where a." + idName + " <(");
                sql.Append("select min(" + idName + ") from (");
                sql.Append("select top " + (topCount * currPageCount).ToString() + " " + idName + " from " + tableName + " where 1=1 " + andWhere + orderStr);
                sql.Append(") ast) " + joinAndWhere + joinInfoAndWhere + orderJoinStr);
            }
            else
            {
                sql.Append("  where 1=1 " + joinAndWhere + joinInfoAndWhere + orderJoinStr);
            }

            return sql.ToString();

        }

         #endregion

        #region not in 排序

        public static string GetPagingString(int topCount, int currPageCount, string tableName, string idName, string colNames, string orderNameList, string andWhere)
        {
            StringBuilder sql = new StringBuilder();

            string orderStr = "";
            if (orderNameList.Trim() != "")
            {
                orderStr =" order by " + orderNameList;
            }
            if (currPageCount >= 1)
            {
                sql.Append("select top " + topCount.ToString() + "  " + colNames + "  from " + tableName);
                sql.Append(" where " + idName + " not in (");
                sql.Append("select top " + (topCount * currPageCount).ToString() + " " + idName + " from " + tableName + " where 1=1 " + andWhere + orderStr); 
                sql.Append(") " + andWhere + orderStr);
                //select top 3 * from [table] where id >(select max(id) from (select top 3 id from [table] where 1=1 )ast)
            }
            else
            {
                sql.Append("select top " + topCount.ToString() + "  *  from " + tableName);
                sql.Append("  where 1=1 " + andWhere + orderStr);
            }


            return sql.ToString();
        }
        #endregion


    }
}

